﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace new_get_info
{
    public class Program
    {
        DBconnection myDB;

        DataTable MyQuery(string cID)
        {
            int i, j;
            
            string query = "";
            DataTable dt = new DataTable();
            try
            {
                myDB = new DBconnection();

                query = @" SELECT DISTINCT a.teacher_id,  a.subject_type_id, a.class_id, c.total_slot, c.max_continuous_slot, c.subject_weight,c.subject_name
                        FROM teacher_subject_type_allocation AS a, subject_type AS b, subject AS c, class AS d
                        WHERE d.class_id='@cID'
                        AND d.class_teacher_id=a.teacher_id
                        AND a.subject_type_id=b.subject_type_id
                        AND b.subject_code=c.subject_code
                        AND a.class_id=d.class_id

                        UNION
                        select * from(select * from(SELECT DISTINCT a.teacher_id, a.subject_type_id, a.class_id, c.total_slot, c.max_continuous_slot, c.subject_weight, c.subject_name
                        FROM teacher_subject_type_allocation AS a, subject_type AS b, subject AS c, class AS d
                        WHERE d.class_id='@cID'
                        AND NOT d.class_teacher_id=a.teacher_id
                        AND a.subject_type_id=b.subject_type_id
                        AND b.subject_code=c.subject_code
                        AND a.class_id=d.class_id
                        ANd NOT b.subject_code='S9'
                        union
                        select m.teacher_id,  m.subject_type_id, m.class_id, m.total_slot, m.max_continuous_slot, m.subject_weight,m.subject_name
                        from(SELECT distinct b.class_id,c.total_slot, c.max_continuous_slot,c.subject_weight,a.subject_code,
                        case when a.subject_code='S9' then 'S91' end as subject_type_id ,
                        case when a.subject_code='S9' then 'Aesthetic' end as subject_name,
                        case when a.subject_code='S9'  then 'T_A' end as teacher_id
                        FROM subject_type as a , teacher_subject_type_allocation as b, subject as c
                        where a.subject_code='S9'  and a.subject_code=c.subject_code) as m where m.class_id='@cID')
                        as l order by  subject_weight, teacher_id)as k";
                query = query.Replace("@cID", cID);
                dt = myDB.Search(query);

                String[,] table = new String[dt.Rows.Count, dt.Columns.Count];

                for (i = 0; i < dt.Rows.Count; i++)
                {
                    for (j = 0; j < dt.Columns.Count; j++)
                    {
                        table[i, j] = dt.Rows[i][j].ToString();

                        Console.Write(table[i, j] + "\t");
                    }
                    Console.Write("\n");
                }
                Console.ReadLine();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;

        }

        public static void Main(string[] args)
        {

            Console.WriteLine("Enter the Class Id:");
            string cID = Console.ReadLine();

            Program a = new Program();
            a.MyQuery(cID);

        }
    }
}
